<?php 
/**
 * list of positions 
 */

class PJobpositionModelPositions extends JModelLegacy 
{
   public function getList($userid, $dir = '') {
      $jobids = JRequest::getString('jobids', null);      
      $app = PFactory::getApplication();
      //sort by publish date or num_applies
      $listorders = PJobpositionHelper::getListOrder();
      $jobpositiondir = $app->getUserState('jobpositiondir', null);
      $jobdir = empty($dir) ? strtolower(JArrayHelper::getValue($jobpositiondir, 'jobsdir', 'all')) : $dir;
      $keyword = JArrayHelper::getValue($jobpositiondir, 'jobskeyword', null); 
      $orderby = JArrayHelper::getValue($jobpositiondir, 'orderby');
      $joinSql = '';
      $searchSql = '';
      $settopsql = '';
      $db = PFactory::getDbo();    
      if(!empty($orderby)){
         $settopsql .= 'tjff.folder_id DESC';
      }else{
         $settopsql .= 'tjff.created DESC';
      }
      if(!empty($jobids)) {
         $jobids = explode(',', $jobids);
         JArrayHelper::toInteger($jobids);
      }
      
      if (is_numeric($jobdir) && $jobdir > 0) {
         $joinSql .= " LEFT JOIN *PREFIX*hr_job_folder AS jfd ON (jfd.folder_id=" . intval($jobdir) . " AND jfd.owner=" . $db->quote(OC_User::getUser()) . " )
                       LEFT JOIN *PREFIX*hr_job_folder_files AS jffd ON (jffd.folder_id=jfd.folder_id AND jffd.job_id=j.id) ";
         $searchSql .= " AND jffd.folder_id IS NOT NULL";
      }
      
      if(isset($jobpositiondir['jobsmatch'])){
         if($jobpositiondir['jobsmatch']=='unset'){
            $searchSql .=' AND j.online_interview_requirements =""';
         }
         if($jobpositiondir['jobsmatch']=='set'){
            $searchSql .=' AND j.online_interview_requirements !=""';
         }
      }
      if(isset($jobpositiondir['jobssyns'])){
         $searchSql .=' AND j.syncstatus IN('.$jobpositiondir['jobssyns'].')';
      } 
      if(isset($jobpositiondir['jobsarea'])){
         $statearray=array();
         $cityarray=array();
         //special cities like shanghai,chongqing,beijing,tianjing need to be dealed with.
         $specialcityarray=array('100','101','102','103');
         $job_area=explode(',', $jobpositiondir['jobsarea']);
         
         foreach ($job_area as $key => $list){
            $list=explode('_', $list);
            if(!empty($list[1]) && !in_array($list[1], $specialcityarray)){
               $cityarray[]=$list[1];
            }else{
               $statearray[]=$list[0];
            }      
         }
         if(count($cityarray)>0 && count($statearray)>0){
            $searchSql .= ' AND (j.id_state IN('.implode(',', $statearray).') OR j.id_city IN('.implode(',', $cityarray).'))';
         }elseif(count($statearray)>0 && count($cityarray)<=0){
            $searchSql .= ' AND j.id_state IN('.implode(',', $statearray).')';
         }elseif(count($cityarray)>0 && count($statearray)<=0){
            $searchSql .= ' AND j.id_city IN('.implode(',', $cityarray).')';
         }       
      }
	  
      if(isset($jobpositiondir['jobssource'])){
         $job_source = explode(',', $jobpositiondir['jobssource']);
         $searchSql .= " AND j.source REGEXP " . $db->quote(implode('|', $job_source));
      }

      $masteruser = PFactory::isMasterUser();
      if(PCompanyHelper::isJobManager() && !$masteruser && ($jobdir=='all' || $jobdir=='trashed')) {
         $subuser = PCompanyHelper::getSubUser();
         if(!empty($subuser) && !empty($subuser->assignedjobs)) {
            if(!empty($jobids)) {
               $jobids = array_intersect($jobids, $subuser->assignedjobs);
               if(!empty($jobids)) 
                  $searchSql .= ' AND j.id IN (' . implode(',', $jobids) . ') ';
               else
                  $searchSql .=' AND j.id IN (0)';
            }
            else   
               $searchSql .= ' AND j.id IN (' . implode(',', $subuser->assignedjobs) . ') ';
         } else {
            return null;
         }
      }
      
      if(!empty($jobids) && $masteruser) {
         $searchSql .= ' AND j.id IN (' . implode(',', $jobids) . ') ';
      }

      if($jobdir=='active') {
         $searchSql .= ' AND j.is_active="y"';
      }
      
      if($jobdir=='closed') {
         $searchSql .= ' AND j.is_active="n"';
      }
      
      if($jobdir=='expired') {
         $searchSql .= ' AND j.expire_date!="0000-00-00 00:00:00" AND j.expire_date < NOW() ';
      }
      if(!empty($keyword)) {
         $searchSql .= " AND (j.job_title LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR j.phone LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR j.email LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR j.state LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR j.city LIKE " . $db->quote('%' . $keyword. '%') .
                       " OR j.ikey LIKE " . $db->quote('%' . $keyword. '%') . ")";
      }
      
      $query =  'SELECT SQL_CALC_FOUND_ROWS j.*, js.specialization, jje.exp_name, jst.salary_type, pj.sheetid as has_persontest, ' . 
                'ps.shorttitle as persontest_shorttitle, jjp.position, IF(jff.folder_id IS NULL, 0, 1) AS is_saved, IF(tjff.folder_id IS NULL, 0, 1) AS is_settop,' .
                ($masteruser ? ' 1 as admin, ' : ' IF(jm.jobid IS NULL, 0, 1) as admin, ') .
                ($masteruser ? \OCP\PERMISSION_ALL . ' as permissions, ' : 'IF(jm.jobid IS NULL, ' . \OCP\PERMISSION_READ . ', ' . \OCP\PERMISSION_ALL . ') as permissions, ') .
                   '(SELECT SUBSTRING_INDEX(GROUP_CONCAT(u.displayname), ",",2) FROM *PREFIX*users_extended AS ue
                     LEFT JOIN *PREFIX*users AS u ON u.uid=ue.uid
                     LEFT JOIN *PREFIX*hr_jobmanager AS jm ON jm.manager=ue.uid
                     WHERE ue.block=0 AND ue.deleted=0 AND jm.jobid=j.id AND !(ue.permissions & ' . $db->quote(PERMISSION_JOB_ADMIN_ALL) . ') AND ue.owner=' . $userid . ') AS adminusers '.
                ' from #__jobs_job as j '.
                ' left join #__jobs_job_spec as js on j.id_job_spec_categ=js.id '.
                ' left join #__jobs_job_exp as jje on jje.id=j.id_job_exp '.
                ' left join #__jobs_salary_type as jst on jst.id=j.id_salary_type '.
                ' left join #__persontest_jobsheet as pj on pj.jobid=j.id '.
                ' left join #__persontest_sheet as ps on ps.id=pj.sheetid '.
                ' left join #__jobs_job_position as jjp on jjp.id=j.id_pos_type '.
                ($masteruser ? '' : ' LEFT JOIN *PREFIX*hr_jobmanager AS jm ON (jm.jobid=j.id AND jm.manager=' . $db->quote(OC_User::getUser()) . ') ') .
                " LEFT JOIN *PREFIX*hr_job_folder AS jf ON (jf.folder_name='我的收藏' AND jf.owner=" . $db->quote(OC_User::getUser()) . " AND jf.system=1)
                  LEFT JOIN *PREFIX*hr_job_folder_files AS jff ON (jff.folder_id=jf.folder_id AND jff.job_id=j.id) " . 
                " LEFT JOIN *PREFIX*hr_job_folder AS tjf ON (tjf.folder_name='置顶' AND tjf.owner=" . $db->quote(OC_User::getUser()) . " AND tjf.system=1)
                  LEFT JOIN *PREFIX*hr_job_folder_files AS tjff ON (tjff.folder_id=tjf.folder_id AND tjff.job_id=j.id) " . $joinSql .
                ' where j.system = 0 AND j.is_active = "y" AND j.employer_id=' . intval($userid) . $searchSql . 
                ($jobdir == 'saved' ? " AND jff.folder_id IS NOT NULL" : "") .
                ($jobdir == 'trashed' ? " AND j.is_trashed=1" : " AND j.is_trashed=0") .
                ' group by j.id ORDER BY '.($jobdir == 'trashed' ? '': $settopsql.',').$listorders.', j.id DESC';
      $limitstart = JRequest::getVar('limitstart', 0, '', 'int');     
      $limit = $app->getUserStateFromRequest('jobpos.limit', 'limit', 50, 'int');
      $limit = $limit > 100 ? 100 : $limit;

      // In case limit has been changed, adjust limitstart accordingly
      $limitstart = ( $limit != 0 ? (floor( $limitstart / $limit ) * $limit) : 0);


      /* try to load from cache */
      $cache = CacheHelper::getCache('app_jobpos');
      // get cached data now 
      list($result, $total) = $cache->call(array($this, '_getList'), $query, intval($limitstart), intval($limit));

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      if(CacheHelper::needTriggers($cache)) {
         $tables = array('#__jobs_job', '#__jobtest_jobsheet', '*PREFIX*hr_company_interview', '#__persontest_jobsheet', '*PREFIX*hr_job_folder_files');
         if(!$masteruser) {
            array_push($tables, '*PREFIX*hr_jobmanager');
            array_push($tables, '*PREFIX*users_extended');
         }
         CacheHelper::setTriggers(OC_User::getUser(), $cache, $tables);
         // if one user's operation has impact on other user view, it's for entire company
         // by linking company id with this caching, triggerRemove on company id will update all subaccounts 
         if(!PFactory::isMasterUser())
            CacheHelper::setTriggers(intval($userid), $cache, $tables);
      }

      $pagination = new PPagination( $total, $limitstart, $limit);
      return array($result, $pagination);
   }

   public function _getList($query, $limitstart=0, $limit=0) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, $limit);
      $result = $db->loadObjectList();

      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $total = $db->loadResult();
      return array($result, $total);
   }
   
   public function getCurrentCompanyJobList(){
      $app=PFactory::getApplication();
      $jobpositiondir = $app->getUserState('jobpositiondir', null);  
      $jobdir = strtolower(JArrayHelper::getValue($jobpositiondir, 'jobsdir', ''));    
      $masteruser = PFactory::getMasterUser();
      $subuser = PCompanyHelper::getSubUser();
      $subSql="";
      $joinSql= "";
      //if jobdir is saved,need search all saved jobs from current company
      if(PCompanyHelper::isJobManager() && !PFactory::isMasterUser()) {
         $subuser = PCompanyHelper::getSubUser();
         if(!empty($jobdir) && !in_array($jobdir, array('saved','company','subcompany'))){
            if(!empty($subuser) && !empty($subuser->assignedjobs)) {
               $subSql .= ' AND jj.id IN (' . implode(',', $subuser->assignedjobs) . ') ';
            } else {
               $subSql .= ' AND jj.id = -1';
            }
         }
      }

      $db = PFactory::getDbo();
      $query=" SELECT CONCAT(jj.id_state,'_',jj.id_city) AS id_state_city,IF(jj.state=jj.city,jj.state,CONCAT(jj.state,jj.city)) AS state_city ".
             " FROM #__jobs_job AS jj".
             " LEFT JOIN *PREFIX*hr_job_folder AS jf ON (jf.folder_name='我的收藏' AND jf.owner=" . $db->quote(OC_User::getUser()) . " AND jf.system=1) ".
             " LEFT JOIN *PREFIX*hr_job_folder_files AS jff ON (jff.folder_id=jf.folder_id AND jff.job_id=jj.id) ".     
             " WHERE jj.system=0 AND jj.is_active='y' AND jj.employer_id=".intval($masteruser->id).$subSql.($jobdir=='saved' ? " AND jff.folder_id IS NOT NULL" : ""); 
      /* try to load from cache */
      $cache = CacheHelper::getCache('app_jobpos');
      // get cached data now
      $data = $cache->call(array($this, '_getCurrentCompanyJobList'), $query);     
      CacheHelper::setTriggers(OC_User::getUser(),$cache, array('#__jobs_job','*PREFIX*hr_job_folder','*PREFIX*hr_job_folder_files'));
      return $data;
   }
    
   public function _getCurrentCompanyJobList($query){
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList('id_state_city', 'state_city');
   }
   
   public function getJobsList($limitstart = 0, $limit = 0) {
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      /* the user must be valid */
      if(!$user || $user->id <= 0) {
        $this->setError('您无权访问该资源');
        return false;
      }
      
      list($starttime, $now) = PResumesHelper::getTimeRange('1d');
      $yesterday_start = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
      $today_end = PFactory::getDate($starttime+2*24*3600)->format('Y-m-d H:i:s');
      
      //list($starttime, $endtime) = PResumesHelper::getTimeRange('3d');
      //$past3d_start = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
      //$past3d_end = PFactory::getDate($endtime)->format('Y-m-d H:i:s');
      
      $subSql = '1=1';
      if(PCompanyHelper::isJobManager() && !PFactory::isMasterUser()) {
         $subuser = PCompanyHelper::getSubUser();
         if(!empty($subuser) && !empty($subuser->assignedjobs)) {
            $subSql = 'jj.id IN (' . implode(',', $subuser->assignedjobs) . ') ';
         } else {
            $subSql = 'jj.id = -1';
         }
      }
     
      /* get job positions dynamic */
      $query = " SELECT SQL_CALC_FOUND_ROWS jj.id AS job_id, jj.job_title, jj.num_applied AS totalresumesnum, " .
             " (SELECT COUNT(*) FROM *PREFIX*hr_jobseeker WHERE job_id=jj.id AND is_trashed=0
                AND date_added >= ".$db->quote($yesterday_start) ." AND date_added < ".$db->quote($today_end).") AS yesterdayresumesnum, " .
             " (SELECT count(*) from *PREFIX*hr_company_interview where job_id=jj.id) AS has_interview ".
             " FROM #__jobs_job AS jj " .
             " LEFT JOIN #__jobs_employer AS e ON e.user_id = jj.employer_id " .
             " LEFT JOIN *PREFIX*hr_job_folder AS jf ON (jf.folder_name='我的收藏' AND jf.owner=" . $db->quote(OC_User::getUser()) . " AND jf.system=1) ".
             " LEFT JOIN *PREFIX*hr_job_folder_files AS jff ON (jff.folder_id=jf.folder_id AND jff.job_id=jj.id) ".
             " LEFT JOIN *PREFIX*preferences AS op ON op.userid =".$db->quote(OC_User::getUser())." AND op.appid='company' AND op.configkey='show_jobsaved'".
             " WHERE jj.is_trashed=0 AND jj.system=0 AND e.user_id = " . $user->id . " AND jj.is_active = 'y' " .
             " AND IF(op.configvalue=1, jff.folder_id IS NOT NULL, 1=1)".
             " AND IF(op.configvalue=0 OR op.configvalue IS NULL,".$subSql.",1=1)".
             " ORDER BY jj.publish_date DESC, jj.id DESC ";
      $limitstart = JRequest::getInt('start', 0);
      // Get the pagination request variables
      $app = PFactory::getApplication();
      $limit = $limit > 0 ? $limit : $app->getUserStateFromRequest('companyjoblist.limit', 'limit', 20, 'int');
      $limit = $limit > 100 ? 100 : $limit;

      // In case limit has been changed, adjust limitstart accordingly
      $limitstart = ( $limit != 0 ? (floor( $limitstart / $limit ) * $limit) : 0);

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_company');
      // get cached data now 
      $joblist = $cache->call(array($this, '_getJobsList'), $query, $limitstart, $limit);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      CacheHelper::setTriggers(OC_User::getUser(), $cache, array('#__jobs_job','*PREFIX*preferences','*PREFIX*hr_company_interview'));
      
      $nopagination = JRequest::getInt('nopagination', 0);
      $total = 0;
      if(!$nopagination) {
         $total = $joblist['total'];
      }

      $pagination = new PPagination($total, $limitstart, $limit);
      return array($joblist['list'], $pagination);
   }

   public function _getJobsList($query, $limitstart = 0, $limit = 0) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, $limit);
      $joblist = $db->loadAssocList('job_id');
      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $total = $db->loadResult();
      return array('list' => $joblist, 'total' => $total);
   }

   /**
    * Get job position from different sources
    * @param  string $source pipapai 51job zhilian etc.
    * @return array
    */
   public function getAllJobPositions($source){
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      switch ($source) {
         case 'pipapai':
            $query = 'SELECT c.id as cid,c.category,s.id as sid,s.specialization,p.id as pid,p.position '.
               'FROM #__jobs_job_categ as c LEFT JOIN #__jobs_job_spec as s ON c.id=s.id_category LEFT JOIN #__jobs_job_position as p ON s.id=p.id_spec ';
               'ORDER BY c.id, s.id, p.id';
            break;
         case 'zhilian':
            $query = 'SELECT c.id as cid,c.category,s.id as sid,s.specialization,p.id as pid,p.position
               FROM #__jobs_job_categ as c
               LEFT JOIN #__jobs_zhilian_job_spec as s ON c.id=s.id_category
               LEFT JOIN #__jobs_zhilian_job_position as p ON s.id=p.id_spec
               ORDER BY c.id, s.id, p.id';
            break;
         case 'jiancai':
            $query = 'SELECT c.id as cid,c.category,s.id as sid,s.specialization,p.id as pid,p.position
               FROM #__jobs_job_categ as c
               LEFT JOIN #__jobs_jiancai_job_spec as s ON c.id=s.id_category
               LEFT JOIN #__jobs_jiancai_job_position as p ON s.id=p.id_spec
               WHERE s.id IS NOT NULL
               ORDER BY c.id, s.id, p.id';
            break;
         case 'lagou':
            $query = 'SELECT c.id as cid,c.category,s.id as sid,s.specialization,p.id as pid,p.position
               FROM #__jobs_job_categ as c
               LEFT JOIN #__jobs_lagou_job_spec as s ON c.id=s.id_category
               LEFT JOIN #__jobs_lagou_job_position as p ON s.id=p.id_spec
               WHERE s.id IS NOT NULL
               ORDER BY c.id, s.id, p.id';
            break;
         default:
            $query = 'SELECT c.id as cid,c.category,s.id as sid,s.specialization,p.id as pid,p.position '.
               'FROM #__jobs_job_categ as c LEFT JOIN #__jobs_job_spec as s ON c.id=s.id_category LEFT JOIN #__jobs_job_position as p ON s.id=p.id_spec ';
               'ORDER BY c.id, s.id, p.id';
            break;
      }
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }
 
   /**
    * Get job folder id based on given name. This only works for folders owned by master user. 
    * @param  string $jobsdir folder name 
    * @return array
    */
   public function getJobsFolderIdByName($jobsdir){
      if(empty($jobsdir))
         return null;
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();

      // join delaytasks to see if the folder name is auto-created foldername based on task
      $query = "SELECT f.folder_id, d.summary ". 
               " FROM *PREFIX*hr_job_folder AS f " . 
               " LEFT JOIN #__delaytasks AS d ON CONCAT('_t', d.id) = f.folder_name" .
               " WHERE f.folder_name =".$db->quote($jobsdir) . " AND f.owner = " . intval($user->id); 

      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   public function getSourcesByJobids($jobids,$action){
      $jobids = json_decode($jobids);
      JArrayHelper::toInteger($jobids);
      if(count($jobids)<=0 || empty($action))
         return null;
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();

      if($action == 'renewjobs'){
         list($starttime, $endtime) = PResumesHelper::getTimeRange();
         $todaystart = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
      
         // join delaytasks to see if the folder name is auto-created foldername based on task
         $query = "SELECT id,source,last_sync_result".
               " FROM #__jobs_job " .
               " WHERE id IN (".implode(',',$jobids).") AND employer_id = ".intval($user->id)." AND IF(source='pipapai',renew < ".$db->quote($todaystart).",1=1)";
   
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $result = $db->loadAssocList();
   
         foreach($result as $row) {
            $sources = explode(',', $row['source']);
            foreach($sources as $s) {
               $sync_result = json_decode($row['last_sync_result']);
               if((!isset($sync_result->$s->renew) || (isset($sync_result->$s->renewtime) && $sync_result->$s->renewtime < $starttime)) && (!isset($sync_result->$s->active) || $sync_result->$s->active ==1)){
                  if(!isset($list[$s]))
                     $list[$s] = array();
                  array_push($list[$s],$row['id']);
               }
            }
         }
      }else if($action == 'autorenewjobs'){
         // join delaytasks to see if the folder name is auto-created foldername based on task
         $query = "SELECT id,source,last_sync_result".
               " FROM #__jobs_job " .
               " WHERE id IN (".implode(',',$jobids).") AND employer_id = ".intval($user->id);
   
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $result = $db->loadAssocList();
   
         foreach($result as $row) {
            $sources = explode(',', $row['source']);
            foreach($sources as $s) {
               $sync_result = json_decode($row['last_sync_result']);
               if(!isset($sync_result->$s->active) || $sync_result->$s->active ==1){
                  if(!isset($list[$s]))
                     $list[$s] = array();
                  array_push($list[$s],$row['id']);
               }
            }
         }
      }
      return isset($list)?$list:null;
   }
}

